// ******************************************************************
// * Copyright(c)  Steven Lu
// * Description   : Entity_SALES_PROD_PLAN_APPLICATION_ITEM
// * CreateDate    : 2011/8/16 10:16:41
// * Creater       : CodeSmith
// * LastChangeDate: 2011/8/16 10:16:41
// * LastChanger   : 2011/8/16 10:16:41
// * Version Info  : 1.0 For ESOL
// ******************************************************************

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Suzsoft.Smart.Data;
using ESOL.Common.Entity;
using ESOL.Common;

namespace ESOL.Business.Sales
{
/// <summary>
    /// SalesProdPlanApplicationItem
    /// </summary>
    public partial class SalesProdPlanApplicationItemBs
    {
        #region GetByID
        /// <summary>
        /// Get Entity
        /// </summary>
        /// <param name="objSalesProdPlanApplicationItemEntity">Entity</param>
        /// <returns>Entity</returns>
        public static SalesProdPlanApplicationItemEntity GetSalesProdPlanApplicationItem(SalesProdPlanApplicationItemEntity objSalesProdPlanApplicationItemEntity)
        {
            return DataAccess.SelectSingle<SalesProdPlanApplicationItemEntity>(objSalesProdPlanApplicationItemEntity);
        }

        public static SalesProdPlanApplicationItemEntity GetProdPlanApplicationItemByID(string itemID)
        {
            if (string.IsNullOrEmpty(itemID))
            {
                return null;
            }
            SalesProdPlanApplicationItemEntity entity = new SalesProdPlanApplicationItemEntity();
            entity.ApplicationItemId = itemID;
            return DataAccess.SelectSingle<SalesProdPlanApplicationItemEntity>(entity);
        }
        public static List<SalesProdPlanApplicationItemEntity> GetProdPlanApplicationItemByAppID(string appID)
        {
            if (string.IsNullOrEmpty(appID))
            {
                return null;
            }
            SalesProdPlanApplicationItemEntity entity = new SalesProdPlanApplicationItemEntity();
            entity.ApplicationId = appID;
            return DataAccess.Select<SalesProdPlanApplicationItemEntity>(entity);
        }
        public static List<SalesProdPlanApplicationItemEntity> GetProdPlanApplicationItemByDeliveryID(string deliveryOrderId)
        {
            if (string.IsNullOrEmpty(deliveryOrderId))
            {
                return null;
            }
            string sql = @"SELECT SPPAI.*
                            FROM SALES_PROD_PLAN_APPLICATION_ITEM SPPAI
                            INNER JOIN SALES_DELIVERY_ITEM SDI ON
	                            SDI.APPLICATION_ITEM_ID = SPPAI.APPLICATION_ITEM_ID
                            WHERE SDI.DELIVERY_ORDER_ID = '" + deliveryOrderId + "'";
            return DataAccess.Select<SalesProdPlanApplicationItemEntity>(sql);
        }
        public static List<SalesProdPlanApplicationItemEntity> GetProdPlanApplicationItemByReceivableID(string receivableOrderId)
        {
            if (string.IsNullOrEmpty(receivableOrderId))
            {
                return null;
            }
            string sql = @"SELECT SPPAI.*
                            FROM SALES_PROD_PLAN_APPLICATION_ITEM SPPAI
                            INNER JOIN SALES_DELIVERY_ITEM SDI ON
	                            SDI.APPLICATION_ITEM_ID = SPPAI.APPLICATION_ITEM_ID
                            INNER JOIN FIN_ACCOUNTS_RECEIVABLE_ITEM FARI ON
	                            FARI.DELIVERY_ITEM_ID = SDI.DELIVERY_ITEM_ID
                            WHERE FARI.RECEIVABLE_ORDER_ID = '" + receivableOrderId + "'";
            return DataAccess.Select<SalesProdPlanApplicationItemEntity>(sql);
        }
        public static DataSet GetDataSetByAppID(string appID)
        {
            if (string.IsNullOrEmpty(appID))
            {
                return null;
            }
            string sql = @"SELECT DISTINCT CONVERT(DECIMAL(18,2),PPAI.PRICE) AS PRICE,PPAI.*,P.*, CONVERT(DECIMAL(18,2),PPAI.QTY*PPAI.PRICE) AS TOTAL_AMOUNT,MCC.CONTACT_NAME--,SOI.ORDER_ITEM_ID
                            FROM SALES_PROD_PLAN_APPLICATION_ITEM PPAI
                            INNER JOIN SALES_PROD_PLAN_APPLICATION PPA ON PPA.APPLICATION_ID = PPAI.APPLICATION_ID
                            LEFT JOIN dbo.SALES_ORDER_ITEM_TO_APPITEM REL ON REL.APPLICATION_ITEM_ID=PPAI.APPLICATION_ITEM_ID
                            LEFT JOIN dbo.SALES_ORDER_ITEM SOI ON SOI.ORDER_ITEM_ID=REL.ORDER_ITEM_ID
                            LEFT JOIN MST_PRODUCT P ON P.PRODUCT_ID = PPAI.PRODUCT_ID
							LEFT JOIN MST_CUSTOMER_CONTACT MCC ON MCC.CONTACT_ID = PPAI.CONTACT_ID
                            WHERE PPA.APPLICATION_ID = '" + appID + "'";
            return DataAccess.SelectDataSet(sql);
        }

        public static DataTable GetByCondition(string condition, string orderBy)
        {
            string sql = @"SELECT top 100 PPAI.*,P.*, PPA.APPLICATION_CODE
                            FROM SALES_PROD_PLAN_APPLICATION_ITEM PPAI
                            INNER JOIN SALES_PROD_PLAN_APPLICATION PPA ON PPA.APPLICATION_ID = PPAI.APPLICATION_ID
                            LEFT JOIN MST_PRODUCT P ON P.PRODUCT_ID = PPAI.PRODUCT_ID
                            WHERE 1=1 ";
            if (string.IsNullOrEmpty(condition) == false)
            {
                sql += condition;
            }
            if (string.IsNullOrEmpty(orderBy) == false)
            {
                sql += " ORDER BY " + orderBy;
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds != null && ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            return null;
        }
        public static DataTable GetByCondition(string condition, string orderBy, string rowCount)
        {
//            string sql = CommonFunctions.GetSelect(rowCount) + @"CONVERT(DECIMAL(18,2),PPAI.QTY * PPAI.PRICE) AS TOTAL_AMOUNT, CONVERT(DECIMAL(18,2),PPAI.PRICE) AS PRICE,
//                            CONVERT(NVARCHAR(10),PPAI.REQUIRED_DELIVERY_DATE,120) AS REQUIRED_DELIVERY_DATE, PPAI.*, PPA.APPLICATION_CODE, PPA.APPLICATION_DATE, P.PRODUCT_CODE, PPA.APPLICATION_TYPE,
//							CONVERT(DECIMAL(18,2),FARI.INVOICE_PRICE) AS INVOICE_PRICE,
//							CONVERT(DECIMAL(18,2),FARI.INVOICE_PRICE * PPAI.QTY) AS INVOICE_TOTAL_AMOUNT,
//							CC.CONTACT_NAME,SO.ORDER_CODE AS SALES_ORDER_CODE,CI.ITEM_NAME AS DELIVERY_TYPE,
//							CONVERT(DECIMAL(18,2),SDI.QTY * SDI.PRICE) AS DELIVERY_AMOUNT,
//							SDI.QTY AS DELIVERY_QTY,PPAI.QTY-PPAI.DELIVERY_QTY AS UNDELIVERY_QTY,CONVERT(NVARCHAR(10),SDF.RECEIVED_DATE,120) AS RECEIVED_DATE,
//							DATEDIFF(DAY,PPAI.REQUIRED_DELIVERY_DATE,SDF.RECEIVED_DATE)  AS DAY_COUNT
//                            FROM SALES_PROD_PLAN_APPLICATION_ITEM PPAI
//                            INNER JOIN SALES_PROD_PLAN_APPLICATION PPA ON PPA.APPLICATION_ID = PPAI.APPLICATION_ID
//							LEFT JOIN SALES_ORDER_ITEM SOI ON SOI.ORDER_ITEM_ID = PPAI.ORDER_ITEM_ID
//                            LEFT JOIN SALES_ORDER SO ON SO.ORDER_ID = SOI.ORDER_ID
//							LEFT JOIN MST_PRODUCT P ON P.PRODUCT_ID = PPAI.PRODUCT_ID
//							LEFT JOIN SALES_DELIVERY_ITEM SDI ON SDI.APPLICATION_ITEM_ID = PPAI.APPLICATION_ITEM_ID AND SDI.DELIVERY_ORDER_ID IN
//							(SELECT SDO.DELIVERY_ORDER_ID FROM SALES_DELIVERY_ORDER SDO WHERE SDO.CUSTOMER_ID = PPA.CUSTOMER_ID AND (SDO.STATUS = 'DO05' OR SDO.STATUS = 'DO06'))
//							LEFT JOIN FIN_ACCOUNTS_RECEIVABLE_ITEM FARI ON FARI.DELIVERY_ITEM_ID = SDI.DELIVERY_ITEM_ID
//							LEFT JOIN MST_CUSTOMER_CONTACT CC ON CC.CONTACT_ID = PPAI.CONTACT_ID
//							LEFT JOIN SALES_DELIVERY_ORDER DO ON DO.DELIVERY_ORDER_ID = SDI.DELIVERY_ORDER_ID
//							LEFT JOIN SALES_DELIVERY_FEEDBACK SDF ON SDF.DELIVERY_ORDER_ID = DO.DELIVERY_ORDER_ID
//							LEFT JOIN MST_COMMON_ITEM CI ON CI.ITEM_CODE = DO.DELIVERY_TYPE
//                            WHERE PPA.STATUS = 'STATUS03'";

            string sql = CommonFunctions.GetSelect(rowCount) + @" CONVERT(DECIMAL(18,2),PPAI.QTY * PPAI.PRICE) AS TOTAL_AMOUNT, CONVERT(DECIMAL(18,2),PPAI.PRICE) AS PRICE,
CONVERT(NVARCHAR(10),PPAI.REQUIRED_DELIVERY_DATE,120) AS REQUIRED_DELIVERY_DATE, PPAI.*, PPA.APPLICATION_CODE, PPA.APPLICATION_DATE, P.PRODUCT_CODE, PPA.APPLICATION_TYPE,
CONVERT(DECIMAL(18,2),FARI.INVOICE_PRICE) AS INVOICE_PRICE,
CONVERT(DECIMAL(18,2),FARI.INVOICE_PRICE * PPAI.QTY) AS INVOICE_TOTAL_AMOUNT,
CC.CONTACT_NAME,CI.ITEM_NAME AS DELIVERY_TYPE,
CONVERT(DECIMAL(18,2),SDI.QTY * SDI.PRICE) AS DELIVERY_AMOUNT,
SDI.QTY AS DELIVERY_QTY,PPAI.QTY-PPAI.DELIVERY_QTY AS UNDELIVERY_QTY,CONVERT(NVARCHAR(10),SDF.RECEIVED_DATE,120) AS RECEIVED_DATE,
DATEDIFF(DAY,PPAI.REQUIRED_DELIVERY_DATE,SDF.RECEIVED_DATE)  AS DAY_COUNT,
--(SELECT COUNT(*) FROM dbo.SALES_ORDER_ITEM_TO_APPITEM WHERE APPLICATION_ITEM_ID=PPAI.APPLICATION_ITEM_ID) AS ORDER_COUNT
(SELECT sum(temp2.QTY) FROM SALES_ORDER_ITEM_TO_APPITEM temp1
inner join SALES_ORDER_ITEM temp2 on temp1.ORDER_ITEM_ID=temp2.ORDER_ITEM_ID
WHERE temp1.APPLICATION_ITEM_ID=PPAI.APPLICATION_ITEM_ID) AS ORDER_COUNT
FROM SALES_PROD_PLAN_APPLICATION_ITEM PPAI
INNER JOIN SALES_PROD_PLAN_APPLICATION PPA ON PPA.APPLICATION_ID = PPAI.APPLICATION_ID
LEFT JOIN MST_PRODUCT P ON P.PRODUCT_ID = PPAI.PRODUCT_ID
LEFT JOIN SALES_DELIVERY_ITEM SDI ON SDI.APPLICATION_ITEM_ID = PPAI.APPLICATION_ITEM_ID AND SDI.DELIVERY_ORDER_ID IN
(SELECT SDO.DELIVERY_ORDER_ID FROM SALES_DELIVERY_ORDER SDO WHERE SDO.CUSTOMER_ID = PPA.CUSTOMER_ID AND (SDO.STATUS = 'DO05' OR SDO.STATUS = 'DO06'))
LEFT JOIN FIN_ACCOUNTS_RECEIVABLE_ITEM FARI ON FARI.DELIVERY_ITEM_ID = SDI.DELIVERY_ITEM_ID
LEFT JOIN MST_CUSTOMER_CONTACT CC ON CC.CONTACT_ID = PPAI.CONTACT_ID
LEFT JOIN SALES_DELIVERY_ORDER DO ON DO.DELIVERY_ORDER_ID = SDI.DELIVERY_ORDER_ID
LEFT JOIN SALES_DELIVERY_FEEDBACK SDF ON SDF.DELIVERY_ORDER_ID = DO.DELIVERY_ORDER_ID
LEFT JOIN MST_COMMON_ITEM CI ON CI.ITEM_CODE = DO.DELIVERY_TYPE
WHERE PPA.STATUS = 'STATUS03'";
            if (string.IsNullOrEmpty(condition) == false)
            {
                sql += condition;
            }
            if (string.IsNullOrEmpty(orderBy) == false)
            {
                sql += " ORDER BY " + orderBy;
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds != null && ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            return null;
        }

        #endregion

        #region Insert
        /// <summary>
        /// Insert Data
        /// </summary>
        /// <param name="objSalesProdPlanApplicationItemEntity">Entity</param>
        /// <returns>true Insert Succeed; false Insert Fail</returns>
        public static bool InsertSalesProdPlanApplicationItem(SalesProdPlanApplicationItemEntity objSalesProdPlanApplicationItemEntity)
        {
            return DataAccess.Insert(objSalesProdPlanApplicationItemEntity);
        }

        public static bool InsertSalesProdPlanApplicationItem(List<SalesProdPlanApplicationItemEntity> objSalesProdPlanApplicationItemEntityList)
        {
            return DataAccess.Insert(objSalesProdPlanApplicationItemEntityList);
        }
        #endregion

        #region Update
        /// <summary>
        /// Update Data
        /// </summary>
        /// <param name="objSalesProdPlanApplicationItemEntity">Entity</param>
        /// <returns>true Update Succeed; false Update Fail</returns>
        public static bool UpdateSalesProdPlanApplicationItem(SalesProdPlanApplicationItemEntity objSalesProdPlanApplicationItemEntity)
        {
            return DataAccess.Update(objSalesProdPlanApplicationItemEntity);
        }

        public static bool UpdateSalesProdPlanApplicationItem(List<SalesProdPlanApplicationItemEntity> objSalesProdPlanApplicationItemEntityList)
        {
            return DataAccess.Update<SalesProdPlanApplicationItemEntity>(objSalesProdPlanApplicationItemEntityList);
        }
        #endregion

        #region Delete
        /// <summary>
        /// Delete Data
        /// </summary>
        /// <param name="objSalesProdPlanApplicationItemEntity">Entity</param>
        /// <returns>true Delete Succeed; false Delete Fail</returns>
        public static bool DeleteSalesProdPlanApplicationItem(SalesProdPlanApplicationItemEntity objSalesProdPlanApplicationItemEntity)
        {
            return DataAccess.Delete(objSalesProdPlanApplicationItemEntity);
        }

        public static bool DeleteSalesProdPlanApplicationItem(List<SalesProdPlanApplicationItemEntity> objSalesProdPlanApplicationItemEntityList)
        {
            return DataAccess.Delete<SalesProdPlanApplicationItemEntity>(objSalesProdPlanApplicationItemEntityList);
        }
        #endregion

        #region GetAll
        /// <summary>
        /// Get All Data
        /// </summary>
        /// <returns>Back All Data<returns>
        public static DataSet GetSalesProdPlanApplicationItemAll()
        {
            return DataAccess.SelectAllDataSet<SalesProdPlanApplicationItemEntity>();
        }
        /// <summary>
        /// Get All Data List
        /// </summary>
        /// <returns>Back All Data List<returns>
        public static List<SalesProdPlanApplicationItemEntity> GetSalesProdPlanApplicationItemAllList()
        {
            return DataAccess.SelectAll<SalesProdPlanApplicationItemEntity>();
        }

        public static DataTable GetDtByContractModel(string sContractModel)
        {
            string sql = @"select * from dbo.SALES_PROD_PLAN_APPLICATION_ITEM
where product_id is not null and product_spec_out like '%"+sContractModel+"%' ";
            return DataAccess.SelectDataSet(sql).Tables[0];
        }
        #endregion

        #region Functions
        public static DataTable GetReportingSalesVolumeByCondition(string sCondition)
        {
            string sql=@"select top 10 sppai.PRODUCT_ID,mp.PRODUCT_CODE,mp.PRODUCT_NAME,sum(sppai.QTY) as TOTAL_QTY
from SALES_PROD_PLAN_APPLICATION sppa
left join SALES_PROD_PLAN_APPLICATION_ITEM sppai on sppa.APPLICATION_ID=sppai.APPLICATION_ID
left join MST_PRODUCT mp on sppai.PRODUCT_ID=mp.PRODUCT_ID 
where 1=1 and sppai.product_id is not null ";
            if(string.IsNullOrEmpty(sCondition)==false)
            {
                sql+=sCondition;
            }
            sql+="group by sppai.PRODUCT_ID,mp.PRODUCT_CODE,mp.PRODUCT_NAME ";
            return DataAccess.SelectDataSet(sql).Tables[0];
        }
        #endregion
    }
	
	
}	

